package rdbms;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import DAO.DatabasePool;
import DAO.ENSequenceDAO;

import controller.Term;

import model.DIConceptDomain;
import model.ENSequence;

public class RDBMSDIConceptDomainDAO {

	private String idConnection;
	private ENSequenceDAO sequence;
	private final String name = "CONCEPT_DOMAIN";

	private static String INSERT = "insert into DI_CONCEPT_DOMAIN(CD_ID, CONCEPT_ID, DOMAIN_ID) values (?, ?, ?)";

	private static String DELETE = "delete from DI_CONCEPT_DOMAIN where CONCEPT_ID = ?";

	private static String DELETE_ALL = "delete from DI_CONCEPT_DOMAIN";

	private static String SELECT_BY_ID = "select CONCEPT_ID, DOMAIN_ID "
			+ "from DI_CONCEPT_DOMAIN where CD_ID = ?";

	private static String SELECT_ALL = "select * from DI_CONCEPT_DOMAIN";

	private static String SELECT_ID_DESC = "SELECT T1.CD_ID, T3.DESCRIPTION FROM DI_CONCEPT_DOMAIN as T1 inner join DI_CONCEPT as T2 on T1.CD_ID = T2.CONCEPT_ID inner join DI_TERM as T3 on T3.TERM_ID = T2.CONCEPT_ID";

	private static String UPDATE = "update DI_CONCEPT_DOMAIN set CONCEPT_ID = ?, DOMAIN_ID = ? where CD_ID = ?";

	public RDBMSDIConceptDomainDAO(final String idConnection,
			final ENSequenceDAO sequence) throws SQLException {
		this.idConnection = idConnection;
		this.sequence = sequence;
	}

	public boolean insert(DIConceptDomain diConceptDomain) throws Exception {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			diConceptDomain.setCdId(sequence
					.nextval(ENSequence.CONCEPT_DOMAIN_ID));
			pst = conn.prepareStatement(INSERT);
			pst.setInt(1, diConceptDomain.getCdId());
			pst.setInt(2, diConceptDomain.getConceptId());
			pst.setInt(3, diConceptDomain.getDomainId());
			System.out.println(diConceptDomain.getCdId());
			System.out.println(diConceptDomain.getConceptId());
			System.out.println(diConceptDomain.getDomainId());
			pst.execute();
			success = true;
		} catch (Exception e) {
			throw new Exception("Exception while inserting " + name, e);
		} finally {
			if (success) {
				conn.commit();
				System.out.println(name + " Insert - Commit");
			} else {
				conn.rollback();
				System.out.println(name + " Insert - Rollback");
			}
			if (pst != null)
				pst.close();
		}
		return success;
	}

	public boolean delete(int id) throws Exception {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(DELETE);
			pst.setInt(1, id);
			pst.execute();
			success = true;
		} catch (Exception e) {
			throw new Exception("Exception while deleting " + name, e);
		} finally {
			if (success) {
				conn.commit();
				System.out.println(name + " - Delete - Commit");
			} else {
				conn.rollback();
				System.out.println(name + " - Delete - Rollback");
			}
			if (pst != null)
				pst.close();
		}
		return success;
	}

	public boolean deleteAll() throws Exception {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(DELETE_ALL);
			pst.execute();
			success = true;
		} catch (Exception e) {
			throw new Exception("Exception while deleting(all) " + name, e);
		} finally {
			if (success) {
				conn.commit();
				System.out.println(name + " - Delete(all) - Commit");
			} else {
				conn.rollback();
				System.out.println(name + " - Delete(all) - Rollback");
			}
			if (pst != null)
				pst.close();
		}
		return success;
	}

	public DIConceptDomain find(int id) throws Exception {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rss = null;
		DIConceptDomain diConceptDomain = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(SELECT_BY_ID);
			pst.setInt(1, id);
			rss = pst.executeQuery();
			if (rss.next()) {
				diConceptDomain = new DIConceptDomain(id, rss.getInt(1),
						rss.getInt(2));
			}
			return diConceptDomain;
		} catch (Exception e) {
			throw new Exception("Exception while retrieving " + name, e);
		} finally {
			if (rss != null)
				rss.close();
			if (pst != null)
				pst.close();
		}
	}

	public boolean update(DIConceptDomain diConceptDomain) throws Exception {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(UPDATE);
			pst.setInt(1, diConceptDomain.getConceptId());
			pst.setInt(2, diConceptDomain.getDomainId());
			pst.setInt(3, diConceptDomain.getCdId());
			pst.execute();
			success = true;
		} catch (Exception e) {
			throw new Exception("Exception while updating " + name, e);
		} finally {
			if (success) {
				conn.commit();
				System.out.println(name + " - Update - Commit");
			} else {
				conn.rollback();
				System.out.println(name + " - Update - Rollback");
			}
			if (pst != null)
				pst.close();
		}
		return success;
	}

	public List<DIConceptDomain> list(int noi) throws Exception {
		List<DIConceptDomain> list = null;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rss = null;
		int ctr = 0;
		try {
			conn = DatabasePool.getConnection(idConnection);
			list = new ArrayList<DIConceptDomain>();
			pst = conn.prepareStatement(SELECT_ALL);
			rss = pst.executeQuery();
			while (rss.next()) {
				if (++ctr > noi)
					break;
				DIConceptDomain diConceptDomain = new DIConceptDomain(
						rss.getInt(1), rss.getInt(2), rss.getInt(3));
				list.add(diConceptDomain);
			}
			return list;
		} catch (Exception e) {
			throw new Exception("Exception while retrieving " + name, e);
		} finally {
			if (rss != null)
				rss.close();
			if (pst != null)
				pst.close();
		}
	}
	
	public HashMap<String, Term> list() throws Exception {
		//HashMap<String, Term> map = new HashMap<String, Term>();
		HashMap<String, Term> map = null;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rss = null;
		Term term = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			map = new HashMap<String, Term>();
			pst = conn.prepareStatement(SELECT_ID_DESC);
			rss = pst.executeQuery();
			while (rss.next()) {
				term = new Term(rss.getString(2), rss.getInt(1));
				map.put(term.getDescription(), term);
			}
			return map;
		} catch (Exception e) {
			throw new Exception("Exception while retrieving " + name, e);
		} finally {
			if (rss != null)
				rss.close();
			if (pst != null)
				pst.close();
		}
	}
	
}
